﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using SARS.DAO.Facades;
using SARS.Entity;

namespace SARS.DAO
{
    public class RoomDAO : IRoomDAO
    {
        private readonly SqlConnection _conn = new SqlConnection(Utils.SarConnectionString);
        private SqlCommand _cmd;
        private SqlDataReader _dr;

        public List<RoomEntity> RetrieveAllRoom()
        {
            var roomList = new List<RoomEntity>();

            try
            {
                _conn.Open();

                _cmd = new SqlCommand("SELECT RoomID, RoomName, RoomType, Description FROM [dbo].[Rooms]", _conn);
                _dr = _cmd.ExecuteReader();

                while (_dr.HasRows && _dr.Read())
                {
                    var room = new RoomEntity
                    {
                        RoomID = Int32.Parse(_dr["RoomID"].ToString()),
                        RoomName = _dr["RoomName"].ToString(),
                        RoomType = _dr["RoomType"].ToString(),
                        Description = _dr["Description"].ToString()
                    };

                    roomList.Add(room);
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Room] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return roomList;
        }

        public RoomEntity RetrieveRoomById(int roomId)
        {
            RoomEntity room = null;

            try
            {
                _conn.Open();

                _cmd =
                    new SqlCommand(
                        "SELECT RoomID, RoomName, RoomType, Description FROM [dbo].[Rooms] WHERE [RoomID] = @roomID",
                        _conn);
                _cmd.Parameters.Add("@roomID", SqlDbType.Int);
                _cmd.Parameters["@roomID"].Value = roomId;

                _dr = _cmd.ExecuteReader(CommandBehavior.SingleRow);

                if (_dr.HasRows && _dr.Read())
                {
                    room = new RoomEntity
                    {
                        RoomID = Int32.Parse(_dr["RoomID"].ToString()),
                        RoomName = _dr["RoomName"].ToString(),
                        RoomType = _dr["RoomType"].ToString(),
                        Description = _dr["Description"].ToString()
                    };
                }

                // Cleanup command and connection objects.
                _dr.Close();
                _cmd.Dispose();
                _conn.Close();
            }
            catch (Exception ex)
            {
                // Add error handling here for debugging.
                // This error message should not be sent back to the caller.
                Trace.WriteLine("[Room] Exception " + ex.Message);
            }
            finally
            {
                _conn.Close();
            }

            return room;
        }
    }
}